//
//  LXC_DataBaseHandle.m
//  12.30_homework_豆瓣电影MVC
//
//  Created by dllo on 16/1/8.
//  Copyright © 2016年 lanou3g. All rights reserved.
//

#import "LXC_DataBaseHandle.h"
#import "ActivityModel.h"
#import "MovieModel.h"
#import "ThreeModel.h"
#import "UserModel.h"

@implementation LXC_DataBaseHandle

+ (LXC_DataBaseHandle *)shareDataBaseHandle {
    
    static LXC_DataBaseHandle *handle;
    
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        handle = [[LXC_DataBaseHandle alloc] init];
    });
    return handle;
}

- (void)openDBMyActivity {
    
    // 获取沙盒路径
    NSString *document = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    // 拼接数据库字符串
    NSString *dbPath = [document stringByAppendingPathComponent:@"MyActivityCollection.sqlite"];
    
    int result = sqlite3_open(dbPath.UTF8String, &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else {
        NSLog(@"数据库打开失败");
    }
    NSLog(@"%@", dbPath);
    
    
}

- (void)createTab {
    
    NSString *sqlStr = @"create table if not exists MyActivityCollection(number integer primary key autoincrement, title text, begin_time text, end_time text, address text, category_name text, wisher_count integer, participant_count integer, image text)";
    // 让数据库执行语句
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"建立活动收藏表成功");
    }else {
        NSLog(@"建立活动收藏表失败");
    }
    
    NSString *sqlStr2 = @"create table if not exists MyMovieCollection(number integer primary key autoincrement, image text, title text, pubdate text, rating text)";
    int result2 = sqlite3_exec(dbPoint, sqlStr2.UTF8String, NULL, NULL, NULL);
    
    if (result2 == SQLITE_OK) {
        NSLog(@"建立电影收藏表成功");
    }else {
        NSLog(@"建立电影收藏表失败");
    }
    
    NSString *sqlStr3 = @"create table if not exists MyUser(number integer primary key autoincrement, userName text, passWord text, email text, telephone text, loginState integer)";
    int result3 = sqlite3_exec(dbPoint, sqlStr3.UTF8String, NULL, NULL, NULL);
    
    if (result3 == SQLITE_OK) {
        NSLog(@"建立用户人信息表成功");
    }else {
        NSLog(@"建立用户人信息表失败");
    }
 
    
}

// 清空缓存 清空数据库
- (void)deleteTable {
    
    NSString *sqlStr = [NSString stringWithFormat:@"delete from MyActivityCollection"];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除活动收藏表成功");
    }else {
        NSLog(@"删除活动收藏表失败");
    }
    
    NSString *sqlStr2 = [NSString stringWithFormat:@"delete from MyMovieCollection"];
    int result2 = sqlite3_exec(dbPoint, sqlStr2.UTF8String, NULL, NULL, NULL);
    
    if (result2 == SQLITE_OK) {
        NSLog(@"删除电影收藏表成功");
    }else {
        NSLog(@"删除电影收藏表失败");
    }

}
// 删除某个收藏
- (void)deleteModel:(NSString *)myTitle tabName:(NSString *)tabName {
    
    NSString *sqlStr = [NSString stringWithFormat:@"delete from %@ where title = '%@'", tabName, myTitle];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else {
        NSLog(@"删除失败");
    }
    
}




////////////////////////////////


// 添加
- (void)insertModelToTab:(NSString *)mySqlStr {
    
    NSString *sqlStr = mySqlStr;
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"收藏成功");
    }else {
        NSLog(@"收藏失败");
    }
}

// 监测是否已收藏
- (BOOL)selectModelFromTab:(NSString *)mySqlStr {
    NSString *sqlStr = mySqlStr;
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            return 1;
        }
    }
    
    return 0;
}

// 从数据库中查询所有的
- (NSMutableArray *)selectAllModelFromTab:(NSString *)mySqlStr {
    
    NSMutableArray *arr = [NSMutableArray array];
    
    NSString *sqlStr = mySqlStr;
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char * title = sqlite3_column_text(stmt, 2);
            MovieModel *model = [MovieModel movie];
            model.title = [NSString stringWithUTF8String:(const char *)title];
            [arr addObject:model];
        }
    }
    return arr;
}


////////////////////////////////





#pragma mark - UserTab
// 添加用户
- (void)insertModelToUserTab:(UserModel *)model {
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into MyUser(userName, password, email, telephone, loginState) values('%@', '%@', '%@', '%@', %ld)", model.userName, model.password, model.email, model.telephone, model.loginState];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"注册成功");
    }else {
        NSLog(@"注册失败");
    }
}
// 查询用户信息是否正确
- (BOOL)selectModelFromUserTab:(UserModel *)model {
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from MyUser where userName = '%@' and password ='%@'", model.userName, model.password];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            return 1;
        }
    }
    return 0;
}
// 更新用户数据
- (void)updateModelFromUserTab:(UserModel *)model loginState:(NSInteger)loginState {
    NSString *sqlStr = [NSString stringWithFormat:@"update MyUser set loginState = %ld where userName = '%@'", loginState, model.userName];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"更新登录状态成功");
    }else {
        NSLog(@"更新登录状态失败");
    }
}

// 查询登录状态
- (NSInteger)selectLoginStateFromUserTab:(NSInteger)loginState {
    
    NSString *sqlStr = [NSString stringWithFormat:@"select loginState from MyUser where loginState = '%ld'", loginState];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            return 1;
        }
    }
    return 0;
}
// 查询登录的用户是谁
- (UserModel *)seletLoginUserFromUserTab:(NSInteger)loginState {
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from MyUser where loginState = '%ld'", loginState];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            const unsigned char * userName = sqlite3_column_text(stmt, 1);
            
            
            
            UserModel *model = [[UserModel alloc] init];
            
            model.userName = [NSString stringWithUTF8String:(const char*)userName];
            
            return model;
        }
        
    }
    return 0;
    
}


#pragma mark - MovieTab
// 添加
- (void)insertModelToMovie:(MovieModel *)model {
    NSString *sqlStr = [NSString stringWithFormat:@"insert into MyMovieCollection(image, title, pubdate, rating) values('%@', '%@', '%@', '%@')", model.image, model.title, model.pubdate, model.rating];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"收藏成功");
    }else {
        NSLog(@"收藏失败");
    }
  
}

// 监测数据库中是否已经收藏了该电影
- (BOOL)selectModelFromMovie:(MovieModel *)model {
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from MyMovieCollection where title = '%@'", model.title];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            return 1;
        }
    }
  
    return 0;
}

// 从数据库中查询所有的
- (NSMutableArray *)selectModelFromMovie {
    NSMutableArray *arr = [NSMutableArray array];
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from MyMovieCollection"];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char * title = sqlite3_column_text(stmt, 2);
            MovieModel *model = [MovieModel movie];
            model.title = [NSString stringWithUTF8String:(const char *)title];
            [arr addObject:model];
        }
    }
    
    return arr;
}

//deleteModelFromMovie:_model
- (void)deletemodelFromMovie:(MovieModel *)model {
    
    NSString *sqlStr = [NSString stringWithFormat:@"delete from MyMovieCollection where title = '%@'", model.title];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else {
        NSLog(@"删除失败");
    }
    
}



#pragma mark - ActivityTab

// 数据库中添加数据
- (void)insertModel:(ActivityModel *)model {
    
//    NSString *wisher_count = [NSString stringWithFormat:@"%@", model.wisher_count];
    NSInteger wisher_count = [model.wisher_count intValue];
    NSInteger participant_count = [model.participant_count intValue];
//    NSString *participant_count = [NSString stringWithFormat:@"%@", model.participant_count];
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into MyActivityCollection(title, begin_time, end_time, address, category_name, wisher_count, participant_count, image) values('%@', '%@', '%@', '%@', '%@', '%ld', '%ld', '%@')", model.title, model.begin_time, model.end_time, model.address, model.category_name, wisher_count, participant_count, model.image] ;
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"收藏成功");
    }else {
        NSLog(@"收藏失败");
    }
 
}

// 用来检测数据库中是否收藏过该活动
- (BOOL)selectModelBool:(ActivityModel *)model {
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from MyActivityCollection where title = '%@'", model.title];
    // 创建一个跟随指针
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            return 1;
        }
    }
    return 0;
}

// 我的活动中用来展示数据库中得收藏
- (NSMutableArray *)selectModel {
    
    NSMutableArray *arr = [NSMutableArray array];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from MyActivityCollection"];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            const unsigned char * title = sqlite3_column_text(stmt, 1);
            const unsigned char * begin_time = sqlite3_column_text(stmt, 2);
            const unsigned char * end_time = sqlite3_column_text(stmt, 3);
            const unsigned char * address = sqlite3_column_text(stmt, 4);
            const unsigned char * category_name = sqlite3_column_text(stmt, 5);
            int wisher_count = sqlite3_column_int(stmt, 6);
            int participant_count = sqlite3_column_int(stmt, 7);
            const unsigned char * image = sqlite3_column_text(stmt, 8);
            
            ActivityModel *model = [ActivityModel activityModel];
            
            printf("%s, %s, %s, %s, %s %d %d %s", title, begin_time, end_time, address, category_name, wisher_count, participant_count, image);
            
            model.title = [NSString stringWithUTF8String:(const char *)title];
            model.begin_time = [NSString stringWithUTF8String:(const char *)begin_time];
            model.end_time = [NSString stringWithUTF8String:(const char *)end_time];
            model.address = [NSString stringWithUTF8String:(const char *)address];
            model.category_name = [NSString stringWithUTF8String:(const char *)category_name];
            model.wisher_count = [NSNumber numberWithInt:wisher_count];
            model.participant_count = [NSNumber numberWithInt:participant_count];
            model.image = [NSString stringWithUTF8String:(const char *)image];
            
            [arr addObject:model];
        }

    }
    return arr;
}



@end
